"""
MySQL数据库封装模块
用于保存AI生成的剧场数据和聊天对话数据到本地数据库
数据库: theam, 用户: root, 密码: root
包含剧场数据和聊天数据的完整管理功能
"""

import json
import mysql.connector
from mysql.connector import Error
from typing import Dict, List, Any, Optional, Tuple
from datetime import datetime
import logging
from contextlib import contextmanager
import asyncio
from concurrent.futures import ThreadPoolExecutor
import uuid

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class TheaterMySQLManager:
    """剧场数据MySQL管理器 - 包含聊天功能"""
    
    def __init__(self, host='localhost', database='theam', user='root', password='root', port=3306):
        self.config = {
            'host': host,
            'database': database,
            'user': user,
            'password': password,
            'port': port,
            'charset': 'utf8mb4',
            'collation': 'utf8mb4_unicode_ci',
            'autocommit': False,
            'raise_on_warnings': False  # 改为False避免警告
        }
        self.executor = ThreadPoolExecutor(max_workers=3)
        
        # 安全初始化
        try:
            self._safe_init_database()
        except Exception as e:
            logger.warning(f"⚠️ 数据库初始化警告: {e}")
            # 不抛出异常，允许程序继续运行

    def _safe_init_database(self):
        """安全的数据库初始化"""
        try:
            # 测试连接
            self._test_connection()
            # 确保数据库存在
            self._ensure_database_exists()
            # 确保表结构存在
            self._ensure_tables_exist()
            logger.info("✅ 数据库初始化完成")
        except Exception as e:
            logger.error(f"❌ 数据库初始化失败: {e}")
            # 不抛出异常，让程序能够继续运行
            pass

    def _test_connection(self):
        """测试MySQL连接"""
        try:
            # 先测试不指定数据库的连接
            temp_config = self.config.copy()
            temp_config.pop('database')
            
            connection = mysql.connector.connect(**temp_config)
            if connection.is_connected():
                connection.close()
                logger.info("✅ MySQL服务器连接测试成功")
            else:
                raise Exception("连接失败")
        except Exception as e:
            logger.error(f"❌ MySQL连接失败: {e}")
            raise

    def _ensure_database_exists(self):
        """确保数据库存在"""
        temp_config = self.config.copy()
        temp_config.pop('database')
        
        try:
            connection = mysql.connector.connect(**temp_config)
            cursor = connection.cursor()
            
            # 检查数据库是否存在
            cursor.execute("SHOW DATABASES LIKE %s", (self.config['database'],))
            exists = cursor.fetchone()
            
            if not exists:
                # 创建数据库
                cursor.execute(f"CREATE DATABASE `{self.config['database']}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
                connection.commit()
                logger.info(f"🗄️ 数据库 '{self.config['database']}' 创建成功")
            else:
                logger.info(f"🗄️ 数据库 '{self.config['database']}' 已存在")
                
        except Exception as e:
            logger.error(f"❌ 数据库操作失败: {e}")
            raise
        finally:
            if cursor:
                cursor.close()
            if connection:
                connection.close()

    def _ensure_tables_exist(self):
        """确保所有表存在"""
        
        tables_sql = {
            # 1. 剧场主表
            "theaters": """
                CREATE TABLE IF NOT EXISTS theaters (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(255) NOT NULL COMMENT '剧场标题',
                    description TEXT COMMENT '剧场描述',
                    era VARCHAR(100) COMMENT '时代背景',
                    location VARCHAR(200) COMMENT '故事地点',
                    plot_summary TEXT COMMENT '情节摘要',
                    features JSON COMMENT '特色列表',
                    author VARCHAR(100) DEFAULT 'AI智能生成器' COMMENT '作者',
                    task_id VARCHAR(50) UNIQUE COMMENT 'AI生成任务ID',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    INDEX idx_title (title),
                    INDEX idx_task_id (task_id),
                    INDEX idx_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='剧场主表'
            """,
            
            # 2. 角色表
            "characters": """
                CREATE TABLE IF NOT EXISTS characters (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    character_id VARCHAR(50) NOT NULL COMMENT '角色标识ID',
                    name VARCHAR(100) NOT NULL COMMENT '角色名称',
                    description TEXT COMMENT '角色描述',
                    avatar VARCHAR(500) COMMENT '头像URL',
                    selected_skills JSON COMMENT '选择的技能列表',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_character_id (character_id),
                    INDEX idx_name (name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表'
            """,
            
            # 3. 剧本节点表
            "script_nodes": """
                CREATE TABLE IF NOT EXISTS script_nodes (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    node_id VARCHAR(50) NOT NULL COMMENT '节点标识ID',
                    title VARCHAR(255) NOT NULL COMMENT '节点标题',
                    content LONGTEXT COMMENT '节点内容(HTML格式)',
                    character_settings JSON COMMENT '角色设置',
                    level INT DEFAULT 0 COMMENT '节点级别',
                    node_order INT DEFAULT 0 COMMENT '节点顺序',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_node_id (node_id),
                    INDEX idx_order (node_order),
                    INDEX idx_level (level)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='剧本节点表'
            """,
            
            # 4. 技能池表
            "skills_pool": """
                CREATE TABLE IF NOT EXISTS skills_pool (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    skill_name VARCHAR(100) NOT NULL COMMENT '技能名称',
                    skill_order INT DEFAULT 0 COMMENT '技能顺序',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_skill_name (skill_name),
                    UNIQUE KEY unique_theater_skill (theater_id, skill_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='技能池表'
            """,
            
            # 5. 生成日志表
            "generation_logs": """
                CREATE TABLE IF NOT EXISTS generation_logs (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    task_id VARCHAR(50) NOT NULL COMMENT '任务ID',
                    theater_id INT COMMENT '剧场ID',
                    stage VARCHAR(50) COMMENT '生成阶段',
                    progress DECIMAL(5,2) DEFAULT 0 COMMENT '进度百分比',
                    message TEXT COMMENT '日志消息',
                    error_message TEXT COMMENT '错误消息',
                    metadata JSON COMMENT '额外元数据',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_task_id (task_id),
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_stage (stage),
                    INDEX idx_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生成日志表'
            """,
            
            # 6. 聊天会话表
            "chat_sessions": """
                CREATE TABLE IF NOT EXISTS chat_sessions (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    session_id VARCHAR(50) UNIQUE NOT NULL COMMENT '会话ID',
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    node_id VARCHAR(50) NOT NULL COMMENT '节点ID',
                    node_title VARCHAR(255) NOT NULL COMMENT '节点标题',
                    node_content LONGTEXT COMMENT '节点内容',
                    player_character_id VARCHAR(50) NOT NULL COMMENT '玩家角色ID',
                    player_character_name VARCHAR(100) COMMENT '玩家角色名称',
                    character_settings JSON COMMENT '角色设置',
                    total_messages INT DEFAULT 0 COMMENT '总消息数',
                    ai_responses_count INT DEFAULT 0 COMMENT 'AI回复数',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后活跃时间',
                    is_active BOOLEAN DEFAULT TRUE COMMENT '是否活跃',
                    session_data JSON COMMENT '会话额外数据',
                    
                    INDEX idx_session_id (session_id),
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_node_id (node_id),
                    INDEX idx_last_active (last_active),
                    INDEX idx_is_active (is_active),
                    INDEX idx_theater_node (theater_id, node_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='聊天会话表'
            """,
            
            # 7. 聊天消息表
            "chat_messages": """
                CREATE TABLE IF NOT EXISTS chat_messages (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    message_id VARCHAR(50) UNIQUE NOT NULL COMMENT '消息ID',
                    session_id VARCHAR(50) NOT NULL COMMENT '会话ID',
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    character_id VARCHAR(50) NOT NULL COMMENT '角色ID',
                    character_name VARCHAR(100) NOT NULL COMMENT '角色名称',
                    message TEXT NOT NULL COMMENT '消息内容',
                    message_type ENUM('dialogue', 'system', 'action') DEFAULT 'dialogue' COMMENT '消息类型',
                    ai_model VARCHAR(50) COMMENT 'AI模型名称',
                    response_time_ms INT COMMENT '响应时间(毫秒)',
                    message_order INT DEFAULT 0 COMMENT '消息顺序',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    metadata JSON COMMENT '消息元数据',
                    
                    INDEX idx_message_id (message_id),
                    INDEX idx_session_id (session_id),
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_character_id (character_id),
                    INDEX idx_message_type (message_type),
                    INDEX idx_created_at (created_at),
                    INDEX idx_message_order (message_order),
                    INDEX idx_session_order (session_id, message_order)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='聊天消息表'
            """,
            
            # 8. 对话统计表
            "chat_statistics": """
                CREATE TABLE IF NOT EXISTS chat_statistics (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    theater_id INT NOT NULL COMMENT '剧场ID',
                    node_id VARCHAR(50) NOT NULL COMMENT '节点ID',
                    total_sessions INT DEFAULT 0 COMMENT '总会话数',
                    total_messages INT DEFAULT 0 COMMENT '总消息数',
                    total_ai_responses INT DEFAULT 0 COMMENT '总AI回复数',
                    avg_session_duration DECIMAL(10,2) DEFAULT 0 COMMENT '平均会话时长(分钟)',
                    avg_messages_per_session DECIMAL(8,2) DEFAULT 0 COMMENT '平均每会话消息数',
                    avg_response_time_ms DECIMAL(10,2) DEFAULT 0 COMMENT '平均响应时间(毫秒)',
                    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
                    
                    UNIQUE KEY unique_theater_node (theater_id, node_id),
                    INDEX idx_theater_id (theater_id),
                    INDEX idx_last_updated (last_updated)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='对话统计表'
            """
        }

        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                
                for table_name, sql in tables_sql.items():
                    try:
                        cursor.execute(sql)
                        logger.info(f"✅ 表 '{table_name}' 检查完成")
                    except mysql.connector.Error as e:
                        if e.errno in [1050, 1061]:  # 表已存在或索引已存在
                            logger.info(f"📋 表 '{table_name}' 已存在")
                        else:
                            logger.warning(f"⚠️ 表 '{table_name}' 处理警告: {e}")
                
                # 尝试添加外键约束（如果不存在）
                self._add_foreign_keys_if_not_exist(cursor)
                
                connection.commit()
                logger.info("🎯 所有表结构检查完成")
                
        except Exception as e:
            logger.error(f"❌ 创建表失败: {e}")
            raise
    def _add_foreign_keys_if_not_exist(self, cursor):
        """添加外键约束（如果不存在）- 改进版"""
        foreign_keys = [
            # 剧场相关外键
            {
                "table": "characters",
                "constraint": "fk_characters_theater",
                "sql": "ALTER TABLE characters ADD CONSTRAINT fk_characters_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            },
            {
                "table": "script_nodes", 
                "constraint": "fk_script_nodes_theater",
                "sql": "ALTER TABLE script_nodes ADD CONSTRAINT fk_script_nodes_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            },
            {
                "table": "skills_pool",
                "constraint": "fk_skills_pool_theater", 
                "sql": "ALTER TABLE skills_pool ADD CONSTRAINT fk_skills_pool_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            },
            
            # 聊天相关外键
            {
                "table": "chat_sessions",
                "constraint": "fk_chat_sessions_theater",
                "sql": "ALTER TABLE chat_sessions ADD CONSTRAINT fk_chat_sessions_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            },
            {
                "table": "chat_messages",
                "constraint": "fk_chat_messages_session",
                "sql": "ALTER TABLE chat_messages ADD CONSTRAINT fk_chat_messages_session FOREIGN KEY (session_id) REFERENCES chat_sessions(session_id) ON DELETE CASCADE"
            },
            {
                "table": "chat_messages",
                "constraint": "fk_chat_messages_theater",
                "sql": "ALTER TABLE chat_messages ADD CONSTRAINT fk_chat_messages_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            },
            {
                "table": "chat_statistics",
                "constraint": "fk_chat_statistics_theater",
                "sql": "ALTER TABLE chat_statistics ADD CONSTRAINT fk_chat_statistics_theater FOREIGN KEY (theater_id) REFERENCES theaters(id) ON DELETE CASCADE"
            }
        ]
        
        for fk in foreign_keys:
            try:
                # 首先检查外键约束是否已存在
                check_sql = """
                SELECT COUNT(*) 
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = %s 
                AND TABLE_NAME = %s 
                AND CONSTRAINT_NAME = %s
                """
                
                cursor.execute(check_sql, (self.config['database'], fk['table'], fk['constraint']))
                exists = cursor.fetchone()[0] > 0
                
                if exists:
                    logger.info(f"📋 外键约束 '{fk['constraint']}' 已存在")
                else:
                    # 外键不存在，尝试创建
                    cursor.execute(fk["sql"])
                    logger.info(f"✅ 外键约束 '{fk['constraint']}' 添加成功")
                    
            except mysql.connector.Error as e:
                if e.errno == 1826:  # 重复的外键约束名
                    logger.info(f"📋 外键约束 '{fk['constraint']}' 已存在（重复约束名）")
                elif e.errno == 1005:  # 外键约束错误
                    logger.warning(f"⚠️ 外键约束 '{fk['constraint']}' 无法添加: {e}")
                elif e.errno == 1061:  # 重复的键名
                    logger.info(f"📋 外键约束 '{fk['constraint']}' 已存在（重复键名）")
                elif e.errno == 1022:  # 重复的键
                    logger.info(f"📋 外键约束 '{fk['constraint']}' 已存在（重复键）")
                else:
                    logger.warning(f"⚠️ 外键约束 '{fk['constraint']}' 添加失败: {e}")
            except Exception as e:
                logger.error(f"❌ 检查外键约束 '{fk['constraint']}' 时发生错误: {e}")
    

    @contextmanager
    def get_connection(self):
        """获取数据库连接的上下文管理器"""
        connection = None
        try:
            connection = mysql.connector.connect(**self.config)
            yield connection
        except Error as e:
            if connection:
                connection.rollback()
            logger.error(f"❌ 数据库连接错误: {e}")
            raise
        finally:
            if connection and connection.is_connected():
                connection.close()

    # ==================== 剧场数据相关方法 ====================

    def save_theater_data(self, task_id: str, theater_data: Dict[str, Any]) -> Dict[str, Any]:
        """保存完整的剧场数据"""
        start_time = datetime.now()
        
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                
                try:
                    # 1. 保存剧场主信息
                    theater_info = theater_data.get('theater_info', {})
                    theater_id = self._save_theater_info(cursor, task_id, theater_info)
                    
                    # 2. 保存角色数据
                    characters_data = theater_data.get('characters', [])
                    character_count = self._save_characters(cursor, theater_id, characters_data)
                    
                    # 3. 保存剧本节点
                    script_nodes = theater_data.get('script_nodes', []) 
                    nodes_count = self._save_script_nodes(cursor, theater_id, script_nodes)
                    
                    # 4. 保存技能池
                    available_skills = theater_data.get('available_skills', [])
                    skills_count = self._save_skills_pool(cursor, theater_id, available_skills)
                    print(f"🔍 数据保存统计:")
                    print(f"   Theater ID: {theater_id}")
                    print(f"   Characters: {character_count} 个")
                    print(f"   Script Nodes: {nodes_count} 个") 
                    print(f"   Skills: {skills_count} 个")
                    # 5. 记录成功日志
                    self._log_generation_result(cursor, task_id, theater_id, "SUCCESS", 
                                              "数据保存成功", {
                                                  "character_count": character_count,
                                                  "nodes_count": nodes_count,
                                                  "skills_count": skills_count,
                                                  "duration_seconds": (datetime.now() - start_time).total_seconds()
                                              })
                    
                    # 提交事务
                    connection.commit()
                    
                    result = {
                        "success": True,
                        "theater_id": theater_id,
                        "task_id": task_id,
                        "saved_data": {
                            "theater": theater_info.get('title', 'N/A'),
                            "characters": character_count,
                            "script_nodes": nodes_count,
                            "skills": skills_count
                        },
                        "duration_seconds": (datetime.now() - start_time).total_seconds(),
                        "message": f"剧场数据保存成功 (ID: {theater_id})"
                    }
                    
                    logger.info(f"🎉 剧场数据保存成功: Theater ID {theater_id}")
                    return result
                    
                except Exception as e:
                    connection.rollback()
                    error_msg = f"保存数据时发生错误: {str(e)}"
                    logger.error(f"❌ {error_msg}")
                    
                    return {
                        "success": False,
                        "error": error_msg,
                        "task_id": task_id,
                        "duration_seconds": (datetime.now() - start_time).total_seconds()
                    }
                finally:
                    cursor.close()
                    
        except Exception as e:
            error_msg = f"数据库连接失败: {str(e)}"
            logger.error(f"❌ {error_msg}")
            return {
                "success": False,
                "error": error_msg,
                "task_id": task_id,
                "duration_seconds": (datetime.now() - start_time).total_seconds()
            }

    def _save_theater_info(self, cursor, task_id: str, theater_info: Dict) -> int:
        """保存剧场主信息"""
        insert_sql = """
        INSERT INTO theaters (title, description, era, location, plot_summary, features, author, task_id)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        
        values = (
            theater_info.get('title', '未命名剧场'),
            theater_info.get('description', ''),
            theater_info.get('era', ''),
            theater_info.get('location', ''),
            theater_info.get('plotSummary', ''),
            json.dumps(theater_info.get('features', []), ensure_ascii=False),
            theater_info.get('author', 'AI智能生成器'),
            task_id
        )
        
        cursor.execute(insert_sql, values)
        theater_id = cursor.lastrowid
        logger.info(f"✅ 剧场主信息保存成功 (ID: {theater_id})")
        return theater_id

    def _save_characters(self, cursor, theater_id: int, characters_data: List[Dict]) -> int:
        """保存角色数据"""
        print(f"🔍 _save_characters 调试:")
        print(f"   theater_id: {theater_id}")
        print(f"   characters_data 类型: {type(characters_data)}")
        print(f"   characters_data 长度: {len(characters_data) if isinstance(characters_data, list) else 'N/A'}")

        if not characters_data:
            print("⚠️ characters_data 为空，跳过保存")
            return 0

        # 🔧 详细检查每个角色的数据结构
        for i, char in enumerate(characters_data):
            print(f"   角色 {i+1} 结构: {list(char.keys()) if isinstance(char, dict) else type(char)}")
            if isinstance(char, dict):
                print(f"     - id: {char.get('id', 'MISSING')}")
                print(f"     - name: {char.get('name', 'MISSING')}")
                print(f"     - description: {char.get('description', 'MISSING')[:50]}...")
                print(f"     - avatar: {char.get('avatar', 'MISSING')}")
                print(f"     - selectedSkills: {type(char.get('selectedSkills', []))}")

        insert_sql = """
        INSERT INTO characters (theater_id, character_id, name, description, avatar, selected_skills)
        VALUES (%s, %s, %s, %s, %s, %s)
        """

        values_list = []
        for i, char in enumerate(characters_data):
            try:
                # 🔧 确保字段存在并有默认值
                character_id = char.get('id', f'char-{i+1}')
                name = char.get('name', f'角色{i+1}')
                description = char.get('description', '')
                avatar = char.get('avatar', f"https://ui-avatars.com/api/?name={name}&background=random&color=fff")
                selected_skills = char.get('selectedSkills', [])

                # 🔧 确保 selectedSkills 是可序列化的
                if not isinstance(selected_skills, list):
                    selected_skills = []

                values = (
                    theater_id,
                    character_id,
                    name,
                    description,
                    avatar,
                    json.dumps(selected_skills, ensure_ascii=False)
                )
                values_list.append(values)
                print(f"✅ 角色 {i+1} 数据准备完成: {name}")

            except Exception as e:
                print(f"❌ 角色 {i+1} 数据处理失败: {e}")
                print(f"   原始数据: {char}")
                continue
            
        if not values_list:
            print("❌ 没有有效的角色数据可保存")
            return 0

        try:
            cursor.executemany(insert_sql, values_list)
            count = cursor.rowcount
            print(f"✅ {count} 个角色保存成功")
            return count
        except Exception as e:
            print(f"❌ 角色数据库保存失败: {e}")
            print(f"SQL: {insert_sql}")
            print(f"Values: {values_list[0] if values_list else 'None'}")
            raise

    def _save_script_nodes(self, cursor, theater_id: int, script_nodes: List[Dict]) -> int:
        """保存剧本节点"""
        print(f"🔍 _save_script_nodes 调试:")
        print(f"   theater_id: {theater_id}")
        print(f"   script_nodes 类型: {type(script_nodes)}")
        print(f"   script_nodes 长度: {len(script_nodes) if isinstance(script_nodes, list) else 'N/A'}")
        print(f"   script_nodes 示例: {script_nodes[0] if isinstance(script_nodes, list) and len(script_nodes) > 0 else 'N/A'}")
    
        if not script_nodes:
            return 0
            
        insert_sql = """
        INSERT INTO script_nodes (theater_id, node_id, title, content, character_settings, level, node_order)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        
        values_list = []
        for i, node in enumerate(script_nodes):
            values = (
                theater_id,
                node.get('id', f'node-{i+1}'),
                node.get('title', ''),
                node.get('content', ''),
                json.dumps(node.get('characterSettings', {}), ensure_ascii=False),
                node.get('level', 0),
                i + 1  # 节点顺序
            )
            values_list.append(values)
        
        cursor.executemany(insert_sql, values_list)
        count = cursor.rowcount
        logger.info(f"✅ {count} 个剧本节点保存成功")
        return count

    def _save_skills_pool(self, cursor, theater_id: int, available_skills: List[str]) -> int:
        """保存技能池"""
        if not available_skills:
            return 0
            
        insert_sql = """
        INSERT INTO skills_pool (theater_id, skill_name, skill_order)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE skill_order = VALUES(skill_order)
        """
        
        values_list = []
        for i, skill in enumerate(available_skills):
            if isinstance(skill, str) and skill.strip():
                values = (theater_id, skill.strip(), i + 1)
                values_list.append(values)
        
        if values_list:
            cursor.executemany(insert_sql, values_list)
            count = len(values_list)
        else:
            count = 0
            
        logger.info(f"✅ {count} 个技能保存成功")
        return count

    def _log_generation_result(self, cursor, task_id: str, theater_id: Optional[int], 
                             stage: str, message: str, metadata: Dict = None):
        """记录生成结果日志"""
        insert_sql = """
        INSERT INTO generation_logs (task_id, theater_id, stage, message, metadata)
        VALUES (%s, %s, %s, %s, %s)
        """
        
        values = (
            task_id,
            theater_id,
            stage,
            message,
            json.dumps(metadata or {}, ensure_ascii=False)
        )
        
        cursor.execute(insert_sql, values)

    async def save_theater_data_async(self, task_id: str, theater_data: Dict[str, Any]) -> Dict[str, Any]:
        """异步保存剧场数据"""
        loop = asyncio.get_event_loop()
        return await loop.run_in_executor(
            self.executor, 
            self.save_theater_data, 
            task_id, 
            theater_data
        )

    def get_theater_by_id(self, theater_id: int) -> Optional[Dict]:
        """根据ID获取剧场完整数据"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取剧场基本信息
                cursor.execute("SELECT * FROM theaters WHERE id = %s", (theater_id,))
                theater = cursor.fetchone()
                
                if not theater:
                    return None
                
                # 获取角色
                cursor.execute("SELECT * FROM characters WHERE theater_id = %s ORDER BY id", (theater_id,))
                characters = cursor.fetchall()
                
                # 解析角色的JSON字段
                for char in characters:
                    if char['selected_skills']:
                        char['selected_skills'] = json.loads(char['selected_skills'])
                
                # 获取剧本节点
                cursor.execute("""
                    SELECT * FROM script_nodes 
                    WHERE theater_id = %s 
                    ORDER BY node_order, id
                """, (theater_id,))
                script_nodes = cursor.fetchall()
                
                # 解析节点的JSON字段
                for node in script_nodes:
                    if node['character_settings']:
                        node['character_settings'] = json.loads(node['character_settings'])
                
                # 获取技能池
                cursor.execute("""
                    SELECT skill_name FROM skills_pool 
                    WHERE theater_id = %s 
                    ORDER BY skill_order
                """, (theater_id,))
                skills_result = cursor.fetchall()
                skills = [row['skill_name'] for row in skills_result]
                
                # 解析剧场的JSON字段
                if theater['features']:
                    theater['features'] = json.loads(theater['features'])
                
                return {
                    "theater_info": theater,
                    "characters": characters,
                    "script_nodes": script_nodes,
                    "available_skills": skills
                }
                
        except Exception as e:
            logger.error(f"❌ 获取剧场数据失败 (ID: {theater_id}): {e}")
            return None

    def get_theaters_list(self, limit: int = 20, offset: int = 0) -> Dict:
        """获取剧场列表"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取总数
                cursor.execute("SELECT COUNT(*) as total FROM theaters")
                total = cursor.fetchone()['total']
                
                # 获取剧场列表
                cursor.execute("""
                    SELECT id, title, description, era, location, author, created_at,
                           (SELECT COUNT(*) FROM characters WHERE theater_id = theaters.id) as character_count,
                           (SELECT COUNT(*) FROM script_nodes WHERE theater_id = theaters.id) as node_count,
                           (SELECT COUNT(*) FROM skills_pool WHERE theater_id = theaters.id) as skill_count
                    FROM theaters 
                    ORDER BY created_at DESC 
                    LIMIT %s OFFSET %s
                """, (limit, offset))
                
                theaters = cursor.fetchall()
                
                return {
                    "success": True,
                    "total": total,
                    "theaters": theaters,
                    "limit": limit,
                    "offset": offset
                }
                
        except Exception as e:
            logger.error(f"❌ 获取剧场列表失败: {e}")
            return {
                "success": False,
                "error": str(e),
                "total": 0,
                "theaters": []
            }

    def delete_theater(self, theater_id: int) -> Dict:
        """删除剧场（级联删除相关数据）"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                
                # 检查剧场是否存在
                cursor.execute("SELECT title FROM theaters WHERE id = %s", (theater_id,))
                theater = cursor.fetchone()
                
                if not theater:
                    return {
                        "success": False,
                        "error": "剧场不存在"
                    }
                
                # 删除剧场（外键约束会自动删除相关数据）
                cursor.execute("DELETE FROM theaters WHERE id = %s", (theater_id,))
                connection.commit()
                
                logger.info(f"🗑️ 剧场删除成功 (ID: {theater_id}, 标题: {theater[0]})")
                
                return {
                    "success": True,
                    "message": f"剧场 '{theater[0]}' 删除成功",
                    "theater_id": theater_id
                }
                
        except Exception as e:
            logger.error(f"❌ 删除剧场失败 (ID: {theater_id}): {e}")
            return {
                "success": False,
                "error": str(e)
            }

    def get_generation_logs(self, task_id: str = None, limit: int = 50) -> List[Dict]:
        """获取生成日志"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                if task_id:
                    cursor.execute("""
                        SELECT * FROM generation_logs 
                        WHERE task_id = %s 
                        ORDER BY created_at DESC 
                        LIMIT %s
                    """, (task_id, limit))
                else:
                    cursor.execute("""
                        SELECT * FROM generation_logs 
                        ORDER BY created_at DESC 
                        LIMIT %s
                    """, (limit,))
                
                logs = cursor.fetchall()
                
                # 解析metadata JSON
                for log in logs:
                    if log['metadata']:
                        log['metadata'] = json.loads(log['metadata'])
                
                return logs
                
        except Exception as e:
            logger.error(f"❌ 获取生成日志失败: {e}")
            return []

    # ==================== 聊天数据相关方法 ====================

    def save_chat_session(self, session_data: Dict[str, Any]) -> Dict[str, Any]:
        """保存聊天会话数据"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor()
                
                try:
                    # 插入或更新会话记录
                    session_sql = """
                    INSERT INTO chat_sessions 
                    (session_id, theater_id, node_id, node_title, node_content, 
                     player_character_id, player_character_name, character_settings, 
                     total_messages, ai_responses_count, session_data)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY UPDATE
                    total_messages = VALUES(total_messages),
                    ai_responses_count = VALUES(ai_responses_count),
                    last_active = CURRENT_TIMESTAMP,
                    session_data = VALUES(session_data)
                    """
                    
                    session_values = (
                        session_data['session_id'],
                        session_data['theater_id'],
                        session_data['node_id'],
                        session_data['node_title'],
                        session_data['node_content'],
                        session_data['player_character_id'],
                        session_data.get('player_character_name', ''),
                        json.dumps(session_data.get('character_settings', {}), ensure_ascii=False),
                        session_data['total_messages'],
                        session_data['ai_responses_count'],
                        json.dumps(session_data.get('metadata', {}), ensure_ascii=False)
                    )
                    
                    cursor.execute(session_sql, session_values)
                    
                    # 保存消息
                    if 'messages' in session_data and session_data['messages']:
                        self._save_chat_messages(cursor, session_data['session_id'], 
                                               session_data['theater_id'], session_data['messages'])
                    
                    # 更新统计信息
                    self._update_chat_statistics(cursor, session_data['theater_id'], session_data['node_id'])
                    
                    connection.commit()
                    
                    result = {
                        "success": True,
                        "session_id": session_data['session_id'],
                        "theater_id": session_data['theater_id'],
                        "saved_messages": len(session_data.get('messages', [])),
                        "message": "聊天会话保存成功"
                    }
                    
                    logger.info(f"✅ 聊天会话保存成功: {session_data['session_id']}")
                    return result
                    
                except Exception as e:
                    connection.rollback()
                    error_msg = f"保存聊天会话失败: {str(e)}"
                    logger.error(f"❌ {error_msg}")
                    return {
                        "success": False,
                        "error": error_msg,
                        "session_id": session_data.get('session_id', 'unknown')
                    }
                finally:
                    cursor.close()
                    
        except Exception as e:
            error_msg = f"数据库连接失败: {str(e)}"
            logger.error(f"❌ {error_msg}")
            return {
                "success": False,
                "error": error_msg,
                "session_id": session_data.get('session_id', 'unknown')
            }

    def _save_chat_messages(self, cursor, session_id: str, theater_id: int, messages: List[Dict]):
        """保存聊天消息"""
        if not messages:
            return 0
        
        # 先删除现有消息（重新保存所有消息）
        cursor.execute("DELETE FROM chat_messages WHERE session_id = %s", (session_id,))
        
        insert_sql = """
        INSERT INTO chat_messages 
        (message_id, session_id, theater_id, character_id, character_name, 
         message, message_type, ai_model, response_time_ms, message_order, metadata)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        
        values_list = []
        for i, msg in enumerate(messages):
            # 确保message_id存在
            message_id = msg.get('id') or msg.get('message_id') or str(uuid.uuid4())
            
            values = (
                message_id,
                session_id,
                theater_id,
                msg.get('character_id', ''),
                msg.get('character_name', ''),
                msg.get('message', ''),
                msg.get('message_type', 'dialogue'),
                msg.get('ai_model', None),
                msg.get('response_time_ms', None),
                i + 1,  # message_order
                json.dumps({
                    "timestamp": msg.get('timestamp', datetime.now().isoformat())
                }, ensure_ascii=False)
            )
            values_list.append(values)
        
        cursor.executemany(insert_sql, values_list)
        count = cursor.rowcount
        logger.info(f"✅ {count} 条聊天消息保存成功")
        return count

    def _update_chat_statistics(self, cursor, theater_id: int, node_id: str):
        """更新聊天统计信息"""
        try:
            # 计算统计数据
            stats_sql = """
            SELECT 
                COUNT(DISTINCT s.session_id) as total_sessions,
                COUNT(m.id) as total_messages,
                COUNT(CASE WHEN m.message_type = 'dialogue' AND m.character_id != s.player_character_id THEN 1 END) as total_ai_responses,
                AVG(TIMESTAMPDIFF(MINUTE, s.created_at, s.last_active)) as avg_session_duration,
                AVG(s.total_messages) as avg_messages_per_session,
                AVG(m.response_time_ms) as avg_response_time_ms
            FROM chat_sessions s
            LEFT JOIN chat_messages m ON s.session_id = m.session_id
            WHERE s.theater_id = %s AND s.node_id = %s
            """
            
            cursor.execute(stats_sql, (theater_id, node_id))
            stats = cursor.fetchone()
            
            if stats:
                # 插入或更新统计记录
                update_stats_sql = """
                INSERT INTO chat_statistics 
                (theater_id, node_id, total_sessions, total_messages, total_ai_responses,
                 avg_session_duration, avg_messages_per_session, avg_response_time_ms)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                total_sessions = VALUES(total_sessions),
                total_messages = VALUES(total_messages),
                total_ai_responses = VALUES(total_ai_responses),
                avg_session_duration = VALUES(avg_session_duration),
                avg_messages_per_session = VALUES(avg_messages_per_session),
                avg_response_time_ms = VALUES(avg_response_time_ms),
                last_updated = CURRENT_TIMESTAMP
                """
                
                cursor.execute(update_stats_sql, (
                    theater_id, node_id,
                    stats[0] or 0,  # total_sessions
                    stats[1] or 0,  # total_messages
                    stats[2] or 0,  # total_ai_responses
                    stats[3] or 0,  # avg_session_duration
                    stats[4] or 0,  # avg_messages_per_session
                    stats[5] or 0   # avg_response_time_ms
                ))
                
                logger.info(f"✅ 聊天统计信息已更新: theater_id={theater_id}, node_id={node_id}")
                
        except Exception as e:
            logger.error(f"❌ 更新聊天统计失败: {e}")

    async def save_chat_session_async(self, session_data: Dict[str, Any]) -> Dict[str, Any]:
        """异步保存聊天会话数据"""
        loop = asyncio.get_event_loop()
        return await loop.run_in_executor(
            self.executor, 
            self.save_chat_session, 
            session_data
        )

    def get_chat_sessions_by_theater(self, theater_id: int, limit: int = 50, offset: int = 0) -> Dict:
        """获取剧场的所有聊天会话"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取总数
                cursor.execute("""
                    SELECT COUNT(*) as total 
                    FROM chat_sessions 
                    WHERE theater_id = %s
                """, (theater_id,))
                total = cursor.fetchone()['total']
                
                # 获取会话列表
                cursor.execute("""
                    SELECT 
                        s.session_id,
                        s.node_id,
                        s.node_title,
                        s.player_character_name,
                        s.total_messages,
                        s.ai_responses_count,
                        s.created_at,
                        s.last_active,
                        s.is_active,
                        t.title as theater_title
                    FROM chat_sessions s
                    JOIN theaters t ON s.theater_id = t.id
                    WHERE s.theater_id = %s
                    ORDER BY s.last_active DESC
                    LIMIT %s OFFSET %s
                """, (theater_id, limit, offset))
                
                sessions = cursor.fetchall()
                
                return {
                    "success": True,
                    "total": total,
                    "sessions": sessions,
                    "limit": limit,
                    "offset": offset
                }
                
        except Exception as e:
            logger.error(f"❌ 获取剧场聊天会话失败: {e}")
            return {
                "success": False,
                "error": str(e),
                "total": 0,
                "sessions": []
            }

    def get_chat_messages_by_session(self, session_id: str) -> Dict:
        """获取会话的所有消息"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取会话信息
                cursor.execute("""
                    SELECT s.*, t.title as theater_title
                    FROM chat_sessions s
                    JOIN theaters t ON s.theater_id = t.id
                    WHERE s.session_id = %s
                """, (session_id,))
                session_info = cursor.fetchone()
                
                if not session_info:
                    return {"success": False, "error": "会话不存在"}
                
                # 获取消息
                cursor.execute("""
                    SELECT 
                        message_id,
                        character_id,
                        character_name,
                        message,
                        message_type,
                        ai_model,
                        response_time_ms,
                        created_at,
                        metadata
                    FROM chat_messages
                    WHERE session_id = %s
                    ORDER BY message_order, created_at
                """, (session_id,))
                
                messages = cursor.fetchall()
                
                # 解析metadata
                for msg in messages:
                    if msg['metadata']:
                        msg['metadata'] = json.loads(msg['metadata'])
                
                return {
                    "success": True,
                    "session_info": session_info,
                    "messages": messages,
                    "total_messages": len(messages)
                }
                
        except Exception as e:
            logger.error(f"❌ 获取聊天消息失败: {e}")
            return {
                "success": False,
                "error": str(e)
            }

    def get_chat_statistics_by_theater(self, theater_id: int) -> Dict:
        """获取剧场的聊天统计信息"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取统计信息
                cursor.execute("""
                    SELECT 
                        cs.*,
                        t.title as theater_title,
                        sn.title as node_title
                    FROM chat_statistics cs
                    JOIN theaters t ON cs.theater_id = t.id
                    LEFT JOIN script_nodes sn ON cs.theater_id = sn.theater_id AND cs.node_id = sn.node_id
                    WHERE cs.theater_id = %s
                    ORDER BY cs.total_messages DESC
                """, (theater_id,))
                
                statistics = cursor.fetchall()
                
                # 获取总体统计
                cursor.execute("""
                    SELECT 
                        SUM(total_sessions) as total_sessions,
                        SUM(total_messages) as total_messages,
                        SUM(total_ai_responses) as total_ai_responses,
                        AVG(avg_session_duration) as overall_avg_duration,
                        AVG(avg_messages_per_session) as overall_avg_messages,
                        AVG(avg_response_time_ms) as overall_avg_response_time
                    FROM chat_statistics
                    WHERE theater_id = %s
                """, (theater_id,))
                
                overall_stats = cursor.fetchone()
                
                return {
                    "success": True,
                    "theater_id": theater_id,
                    "node_statistics": statistics,
                    "overall_statistics": overall_stats or {}
                }
                
        except Exception as e:
            logger.error(f"❌ 获取聊天统计失败: {e}")
            return {
                "success": False,
                "error": str(e)
            }

    def get_chat_session_by_theater_node(self, theater_id: int, node_id: str) -> Optional[Dict]:
        """根据剧场ID和节点ID获取最新的聊天会话"""
        try:
            with self.get_connection() as connection:
                cursor = connection.cursor(dictionary=True)
                
                # 获取最新的会话
                cursor.execute("""
                    SELECT * FROM chat_sessions 
                    WHERE theater_id = %s AND node_id = %s 
                    ORDER BY last_active DESC 
                    LIMIT 1
                """, (theater_id, node_id))
                
                session = cursor.fetchone()
                
                if session:
                    # 获取会话消息
                    messages_result = self.get_chat_messages_by_session(session['session_id'])
                    if messages_result.get('success'):
                        session['messages'] = messages_result['messages']
                    else:
                        session['messages'] = []
                
                return session
                
        except Exception as e:
            logger.error(f"❌ 获取会话失败: {e}")
            return None

    def close(self):
        """关闭资源"""
        if hasattr(self, 'executor'):
            self.executor.shutdown(wait=True)
        logger.info("🔒 MySQL管理器已关闭")

# 创建全局实例
mysql_manager = None

def get_mysql_manager() -> TheaterMySQLManager:
    """获取MySQL管理器实例"""
    global mysql_manager
    if mysql_manager is None:
        mysql_manager = TheaterMySQLManager()
    return mysql_manager

# 测试函数
def test_mysql_connection():
    """测试MySQL连接"""
    try:
        manager = TheaterMySQLManager()
        
        # 测试基本连接
        with manager.get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute("SELECT 1 as test, VERSION() as version, DATABASE() as current_db")
            result = cursor.fetchone()
            cursor.close()
            
        print("✅ MySQL连接测试成功")
        print(f"📊 MySQL版本: {result[1] if result else 'Unknown'}")
        print(f"📊 当前数据库: {result[2] if result else 'Unknown'}")
        
        # 测试获取剧场列表
        theaters_list = manager.get_theaters_list(limit=5)
        print(f"📋 剧场总数: {theaters_list.get('total', 0)}")
        print(f"📋 剧场列表: {len(theaters_list.get('theaters', []))} 条记录")
        
        # 测试聊天表
        with manager.get_connection() as connection:
            cursor = connection.cursor()
            cursor.execute("SHOW TABLES LIKE 'chat_%'")
            chat_tables = [row[0] for row in cursor.fetchall()]
            cursor.close()
        
        print(f"💬 聊天相关表: {chat_tables}")
        
        return True
        
    except Exception as e:
        print(f"❌ MySQL连接测试失败: {e}")
        return False

if __name__ == "__main__":
    print("🧪 运行MySQL模块测试...")
    test_mysql_connection()
